﻿using System;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;
using Ini;
using System.Data.OleDb;

///<summary>
///MyDataOp说明
///数据库操作类，可连接多个数据库
///支持操作：查询，添加，删除，修改，事务
///建立对象：MyDataOp(strSql) 其中strSql为SQL语句
///查询：CreatReader()，CreatDataSet()
///添加，删除，修改：ExecuteCommand()
///事务：DoTran() 支持同时连接两个数据库
///操作完成后自动关闭数据库连接
///</summary>

public class MyDataOp
{

    NLog.Logger log = NLog.LogManager.GetCurrentClassLogger();
    #region 静态成员变量和方法
    private static string strOLEConnectString = "";
    private static string strConnectString = "";
    /// <summary>
    /// 初始化连接字符串
    /// </summary>
    public static void InitConnectString()
    {
        string strSection = "ConnectParam";//区块
        string strKey = "";//键名
        string strDataBaseServer;//数据库名称
        string strUserName;//用户名
        string strPwd;//密码
        string strDataBaseName;//数据库名称
        IniFile inifile = new IniFile(Environment.CurrentDirectory.ToString() + "\\param.ini");

        //读出ini文件
        //数据库服务器
        strKey = "DataBaseServer";
        strDataBaseServer = inifile.IniReadValue(strSection, strKey);

        //用户名
        strKey = "User";
        strUserName = inifile.IniReadValue(strSection, strKey);

        //密码
        strKey = "Password";
        strPwd = inifile.IniReadValue(strSection, strKey);

        //数据库名称
        strKey = "DataBase";
        strDataBaseName = inifile.IniReadValue(strSection, strKey);

        strConnectString = "server=" + strDataBaseServer + ";user id=" + strUserName + ";pwd=" + strPwd + ";database=" + strDataBaseName;

        strKey="OLEConnectString";
        strOLEConnectString=inifile.IniReadValue(strSection,strKey);
    }

    #endregion

    private string strSql;
    private SqlConnection sqlConn;
    OleDbConnection objConnection;
        
    
    /// <summary>
    /// MyDataOp构造函数
    /// </summary>
    public MyDataOp()
    {
    }


    /// <summary>
    /// 执行查询，将查询结果以DataReader的形式返回
    /// </summary>
    /// <param name="strSql">SQL语句</param>
    /// <returns>DataReader</returns>
    public SqlDataReader CreateReader(string strSql)
    {
        sqlConn = new SqlConnection(strConnectString);
        SqlCommand sqlComm = new SqlCommand(strSql, sqlConn);
        sqlConn.Open();
        SqlDataReader sqlReader = sqlComm.ExecuteReader();
        return sqlReader;
    }
    
    /// <summary>
    /// 执行查询操做，将结果以DataSet的形式返回
    /// </summary>
    /// <param name="strSql">SQL语句</param>
    /// <returns>DataSet</returns>
    public DataSet CreateDataSet(string strSql)
    {
        //----SQLServer
        //sqlConn = new SqlConnection(strConnectString);
        //SqlDataAdapter sqlAdpt = new SqlDataAdapter(strSql, sqlConn);
        //DataSet ds = new DataSet();
        //sqlAdpt.Fill(ds);
        //return ds;


        //----Access
        objConnection = new OleDbConnection(strOLEConnectString);
        OleDbDataAdapter oleAdpt = new OleDbDataAdapter(strSql, objConnection);
        DataSet ds = new DataSet();
        oleAdpt.Fill(ds);
        return ds;


    }

    /// <summary>
    /// 执行添加、删除、修改等操作
    /// </summary>
    /// <param name="strSql">SQL语句</param>
    /// <returns>操作是否成功,成功则返回true</returns>
    public bool ExecuteCommand(string strSql)
    {
        //-----SQLServer
        //sqlConn = new SqlConnection(strConnectString);
        //SqlCommand sqlComm = new SqlCommand(strSql, sqlConn);
        //sqlConn.Open();
        //try
        //{
        //    sqlComm.ExecuteNonQuery();
        //    sqlConn.Close();
        //    return true;
        //}
        //catch(Exception ex)
        //{
        //    log.Info(strSql + "\r\n" + ex.Message);
        //    return false;
        //}

        //-----Access
        objConnection = new OleDbConnection(strOLEConnectString);
        OleDbCommand oleComm = new OleDbCommand(strSql, objConnection);
        objConnection.Open();
        try
        {
            oleComm.ExecuteNonQuery();
            objConnection.Close();
            return true;
        }
        catch (Exception ex)
        {
            log.Info(strSql + "\r\n" + ex.Message);
            return false;
        }
    }
    
    /// <summary>
    /// 执行事务处理
    /// </summary>
    /// <param name="arrstrSql">需要执行的SQL语句数组</param>
    /// <returns>操作是否成功，成功则返回true</returns>
    public bool DoTran(string[] arrstrSql)
    {
        //操作结果
        bool blResult = false;

        sqlConn = new SqlConnection(strConnectString);//建立连接
        SqlCommand sqlComm = new SqlCommand();
        SqlTransaction sqlTran;//SqlTransaction类无公开的构造函数

        //打开链接
        sqlConn.Open();
       
        //创建一个事务
        sqlTran = sqlConn.BeginTransaction();


        //从此开始，基于该连接的数据操作都被认为是事务的一部分
        try
        {
            //下面绑定连接和事务对象
            sqlComm.Connection = sqlConn;
            sqlComm.Transaction = sqlTran;

            //设定存储点
            sqlTran.Save("NoUpdate");

            //更新数据
            for (int i = 0; i < arrstrSql.Length; i++)
            {
                sqlComm.CommandText = arrstrSql[i];
                sqlComm.ExecuteNonQuery();
            }
             
            //提交事务
            sqlTran.Commit();
            blResult = true;
        }
        catch (Exception err)
        {
            //更新错误，回滚到指定存储点
            sqlTran.Rollback("NoUpdate");
            blResult = false;
        }
        finally
        {
            sqlConn.Close();      
        }
        return blResult;
    }
}
